/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apt.Bean.LectureEntities;
import org.apt.Bean.LectureVictoryEntities;
import org.apt.connectDB.MyConnect;

/**
 *
 * @author Administrator
 */
public class LectureDao {

    /**
     * function  Get All Lecture
     * @return List of Lecture
     */
    public static List<LectureEntities> getAllLecture() {
        List<LectureEntities> listLecture = new ArrayList<LectureEntities>();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String sql = "select * FROM Lecture";
            con = MyConnect.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            if (rs.next()) {
                while (rs.next()) {
                    LectureEntities lecture = new LectureEntities();
                    lecture.setLectureID(rs.getInt("LectureID"));
                    lecture.setTitle(rs.getString("Title"));
                    lecture.setSummary(rs.getString("Summary"));
                    lecture.setContent(rs.getString("Content"));
                    lecture.setImage(rs.getString("Image"));
                    lecture.setDayPost(rs.getString("DayPost"));
                    lecture.setStatus(rs.getBoolean("Status"));
                    lecture.setDescription(rs.getString("Description"));
                    listLecture.add(lecture);
                }
            } else {
                return null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return listLecture;
    }

    /**
     * function  Get 10 record per one access
     * @param pageno : number of page )
     * @return list Lecture
     */
    public static List<LectureEntities> getLecture(int pageno) {
        List<LectureEntities> listLecture = new ArrayList<LectureEntities>();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        int pageno2;
        try {
            if (pageno <= 1) {
                pageno = 1;
            } else if (pageno == 2) {
                pageno = 10 + 1;
            } else if (pageno > 2) {
                pageno = (pageno - 1) * 10 + 1;
            }
            pageno2 = pageno + 9;
            con = MyConnect.getConnection();
            st = con.createStatement();
            String sql = "SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY LectureID) AS row,* FROM Lecture) as tblTemp WHERE  row between " + pageno + " AND " + pageno2 + " ";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                LectureEntities lecture = new LectureEntities();
                lecture.setLectureID(rs.getInt("LectureID"));
                lecture.setTitle(rs.getString("Title"));
                lecture.setSummary(rs.getString("Summary"));
                lecture.setContent(rs.getString("Content"));
                lecture.setImage(rs.getString("Image"));
                lecture.setDayPost(rs.getString("DayPost"));
                lecture.setStatus(rs.getBoolean("Status"));
                lecture.setDescription(rs.getString("Description"));
                listLecture.add(lecture);
            }
        } catch (SQLException ex) {
            Logger.getLogger(LectureDao.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("ERROR:" + ex.getCause() + "");
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return listLecture;
    }

    /**
     * function  Get one lecture
     * @param lectureID
     * @return a Lecture
     */
    public static LectureEntities getLectureByID(int lectureID) {
        LectureEntities lecture = new LectureEntities();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String sql = "select * from Lecture where LectureID = " + lectureID + "";
            con = MyConnect.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);

            while (rs.next()) {
                lecture.setLectureID(rs.getInt("LectureID"));
                lecture.setTitle(rs.getString("Title"));
                lecture.setSummary(rs.getString("Summary"));
                lecture.setContent(rs.getString("Content"));
                lecture.setImage(rs.getString("Image"));
                lecture.setDayPost(rs.getString("DayPost"));
                lecture.setStatus(rs.getBoolean("Status"));
                lecture.setDescription(rs.getString("Description"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return lecture;
    }

    /**
     * function get top 5 Lecture
     * @return list Lecture
     */
    public static List<LectureEntities> getTop5Lecture() {
        List<LectureEntities> listLecture = new ArrayList<LectureEntities>();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT top 5 Lecture.LectureID, Lecture.Title FROM Lecture INNER JOIN LectureJoin ON Lecture.LectureID = LectureJoin.LectureID INNER JOIN LectureEvent ON LectureJoin.EventID = LectureEvent.EventID where LectureJoin.Prize = 1";
            con = MyConnect.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                LectureEntities lecture = new LectureEntities();
                lecture.setLectureID(rs.getInt("LectureID"));
                lecture.setTitle(rs.getString("Title"));
                listLecture.add(lecture);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return listLecture;
    }

    /**
     * functiion delete Lecture
     * @param lectuerID
     * @return
     */
    public static boolean deleteLecture(int lectuerID) {
        boolean flag = false;
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String sql = " Delete FROM Lecture where LectureID = " + lectuerID + "";
            con = MyConnect.getConnection();
            st = con.createStatement();
            LectureEntities lecture = getLectureByID(lectuerID);
            if (lecture != null) {
                int resuilt = st.executeUpdate(sql);
                if (resuilt > 0) {
                    flag = true;
                } else {
                    flag = false;
                }
            } else {
                flag = false;
            }

        } catch (SQLException ex) {
            Logger.getLogger(MemberDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            MyConnect.closeConntion(con, st, rs);
        }
        return flag;
    }

    /**
     * Function getlecture victory
     * @param lectureID
     * @return
     */
    public static LectureVictoryEntities getLectureVictory(String lectureID) {
        List<LectureVictoryEntities> listlectureVictory = new ArrayList<LectureVictoryEntities>();
        LectureVictoryEntities lectureVictory = new LectureVictoryEntities();
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT  top(5) Lecture.*,LectureJoin.Prize,LectureEvent.EventName"
                    + " FROM Lecture INNER JOIN "
                    + " LectureJoin ON Lecture.LectureID = LectureJoin.LectureID INNER JOIN "
                    + " LectureEvent ON LectureJoin.EventID = LectureEvent.EventID where LectureJoin.Prize = 1 "
                    + " and Lecture.LectureID ='" + lectureID + "'";
            con = MyConnect.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                //lectureVictory = new LectureVictoryEntities();
                lectureVictory.setLectureID(rs.getInt("LectureID"));
                lectureVictory.setTitle(rs.getString("Title"));
                lectureVictory.setSummary(rs.getString("Summary"));
                lectureVictory.setContent(rs.getString("Content"));
                lectureVictory.setImage(rs.getString("Image"));
                lectureVictory.setDayPost(rs.getString("DayPost"));
                lectureVictory.setStatus(rs.getBoolean("Status"));
                lectureVictory.setDescription(rs.getString("Description"));
                lectureVictory.setEventName(rs.getString("EventName"));
                lectureVictory.setPrize(rs.getInt("Prize"));
                listlectureVictory.add(lectureVictory);
            }
        } catch (SQLException ex) {
            Logger.getLogger(MemberDao.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } finally {
            //MyConnect.closeConntion(con, st, null);
        }
        return lectureVictory;
    }

    public static void main(String[] args) throws SQLException {
        boolean a = deleteLecture(28);
        System.out.println(a);
    }
}
